package com.jdbc.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.joke.util.DateUtil;

public class MysqlLocalConnect {
	private static final Logger logger = LoggerFactory.getLogger(MysqlLocalConnect.class);

	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	private static final String DB_URL = "jdbc:mysql://localhost:3306/joke";
	private static final String USER = "root";
	private static final String PASS = "mysql";
    
    private static String date = DateUtil.formatString(new Date(), "yyyy-MM-dd");
    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private static PreparedStatement prestmt = null;
    
    public static Connection getConnect() {
        try {
        	// 注册 JDBC 驱动
			Class.forName(JDBC_DRIVER);
			// 打开链接
			conn = DriverManager.getConnection(DB_URL,USER,PASS);
			// 执行查询
			stmt = conn.createStatement();
//			logger.info("连接local数据库成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
        return conn;
    }
    public static void close() {
		try {
			if(rs!=null) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try{
		    if(stmt!=null) stmt.close();
		}catch(SQLException se2){
		} 
		
		try{
		    if(prestmt!=null) prestmt.close();
		}catch(SQLException se2){
		} 
		
		try{
		    if(conn!=null) conn.close();
		}catch(SQLException se){
		    se.printStackTrace();
		}
	}
    
    public static Boolean confirmConnect() {
        try {
        	// 注册 JDBC 驱动
			Class.forName(JDBC_DRIVER);
			// 打开链接
			conn = DriverManager.getConnection(DB_URL,USER,PASS);
			// 执行查询
			stmt = conn.createStatement();
		} catch (ClassNotFoundException e) {
			return false;
		} catch (SQLException e) {
			return false;
		}finally {
			close();
		}
        return true;
    }
    
    /**
     * 查询抓取下来的数据（段子、热门、趣图、视频）是否已在本地数据库中存在,如果不存在将图片下载下来，否则不下载
     * (如果不存在则将数据存入joke网的服务器上)
     * @param content
     * @param image
     * @return
     */
    public static Boolean query(String content,String image) {
    	int count = 1;
    	String sql = "select count(1) ct from joke where content = '"+content+"' ";
        try{
        	if(image!=null&&!"".equals(image.trim())) {
        		sql =  sql+" and image = '"+image+"' ";
        	}
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                count  = rs.getInt("ct");
            }
        }catch(SQLException e){
        	logger.error("query,SQLException:"+e.getMessage());
        }catch(Exception e){
        	logger.error("query,Exception:"+e.getMessage());
        }/*finally{
        }*/
       return  count==0?true:false;
    } 
    
    /**
     * 根据日期查询当天的图片（热门、趣图、视频）是否存入joke网的服务器上，如果存入了则将图片上传至阿里云OSS服务器，否则不上传
     * @param imageName    app2301928301.jpg..
     * @param date         yyyy-MM-dd
     * @return
     */
    public static Boolean queryByDay(String imageName,String date) {
    	int count = 1;
    	String sql = "select count(1) ct from joke where image = '"+imageName+"' ";
        try{
        	if(date!=null&&!"".equals(date.trim())) {
        		sql =  sql+" and cdate = '"+date+"' ";
        	}
//        	logger.info(sql);
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                count  = rs.getInt("ct");
            }
           
        }catch(SQLException e){
        	logger.error("queryByDay,SQLException:"+e.getMessage());
        }catch(Exception e){
        	logger.error("queryByDay,Exception:"+e.getMessage());
        }finally{
        }
       return  count==0?false:true;
    } 
    
    /**
     * 将上传到服务器里数据（段子，趣图，热门，视频）的本地备份导入到本地库中
     * @param map
     * @param title
     */
    public static void insert(Map<String,String> map,String title) {
    	getConnect();
        try{
            String sql = "INSERT INTO joke(title,image,content,cdate) VALUES (?, ?, ?, ?);";
            prestmt = conn.prepareStatement(sql);
			for (String str : map.keySet()) {
//				if(query(str,map.get(str))) {
					prestmt.setString(1,title);
					prestmt.setString(2,map.get(str));
					prestmt.setString(3,str);
					prestmt.setString(4, date);
					prestmt.addBatch();
//				}
			}
			prestmt.executeBatch();
//			prestmt.clearBatch();
            logger.info("保存<"+title+">数据到local完成:"+map.size()); 
        }catch(SQLException e){
        	logger.error("insert,SQLException:"+e.getMessage());
        }catch(Exception e){
        	logger.error("insert,Exception:"+e.getMessage());
        }finally{
        	close();
        }
    }
    
    public static void main(String[] args) {
//    	System.out.println(query("2123",null));
	}
}
